package com.dy.dao;

import com.dy.bean.Sugar;
import com.dy.bean.SugarThree;
import com.dy.bean.SugarTwo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT b.name 'name',sum(final_total_amount)'value' FROM order_info o\n" +
            "LEFT JOIN base_province b on o.province_id=b.id \n" +
            "GROUP BY b.name")
    List<Sugar> showMap();

    @Select("SELECT t.name 'name',sum(final_total_amount)'value' FROM order_info o \n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id \n" +
            "LEFT JOIN base_trademark t on s.tm_id = t.id \n" +
            "GROUP BY t.name")
    List<Sugar> showXs();

    @Select("SELECT b1.name,sum(final_total_amount)'value' FROM order_info o \n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id \n" +
            "LEFT JOIN spu_info i on s.spu_id = i.id \n" +
            "LEFT JOIN base_category3 b3 ON i.category3_id = b3.id \n" +
            "LEFT JOIN base_category2 b2 ON b3.category2_id = b2.id \n" +
            "LEFT JOIN base_category1 b1 ON b2.category1_id = b1.id \n" +
            "GROUP BY b1.name")
    List<Sugar> showXszb();

    @Select("SELECT a.newUser,b.oldUser FROM\n" +
            "(SELECT count(*)'newUser' FROM user_info where TIMESTAMPDIFF(MONTH,create_time,NOW())<=1)a,\n" +
            "(SELECT count(*)'oldUser' FROM user_info where TIMESTAMPDIFF(MONTH,create_time,NOW())>1)b")
    List<SugarTwo> showUser();

    @Select("SELECT b2.name,sum(final_total_amount)'value' FROM order_info o \n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id \n" +
            "LEFT JOIN spu_info i on s.spu_id = i.id \n" +
            "LEFT JOIN base_category3 b3 ON i.category3_id = b3.id \n" +
            "LEFT JOIN base_category2 b2 ON b3.category2_id = b2.id \n" +
            "GROUP BY b2.name  ORDER BY sum(o.final_total_amount) desc")
    List<Sugar> showTwo();

    @Select("SELECT CONCAT(SUBSTRING(birthday,3,1),'0后')'name',sum(final_total_amount)'value' FROM user_info u \n" +
            "LEFT JOIN order_info o on u.id = o.user_id\n" +
            "GROUP BY CONCAT(SUBSTRING(birthday,3,1),'0后')")
    List<Sugar> showAge();

    @Select("SELECT yys 'name', sum(final_total_amount)'value'FROM order_info WHERE yys is not null GROUP BY yys")
    List<Sugar> showYys();

    @Select("SELECT b.name,if (u.gender='F','男','女')'gender',sum(final_total_amount)'value' FROM user_info u\n" +
            "LEFT JOIN order_info o on u.id = o.user_id\n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id \n" +
            "LEFT JOIN base_trademark b on s.tm_id = b.id \n" +
            "where b.name is not null \n" +
            "GROUP BY b.name")
    List<SugarThree> showGender();
    @Select("call sp8")
    Double showSum();
    @Select("SELECT i.spu_name'name',sum(final_total_amount)'value' FROM order_info o \n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id \n" +
            "LEFT JOIN spu_info i on s.spu_id = i.id \n" +
            "GROUP BY i.spu_name")
    List<Sugar> showSpu();
}
